Release 10.1A: OpenEdge Data Management:
SQL Reference

Date-time literals

SQL supports special formats for literals to be used in conjunction with date-time data types. Basic predicates and the VALUES clause of INSERT statements can specify date literals directly for comparison and insertion into tables. In other cases, you need to convert date literals to the appropriate date-time data type with the CAST, CONVERT, or TO_DATE scalar functions.

Enclose date-time literals in single quotation marks ( ' ' ).

Notes

All text (names of days, months, ordinal number endings) in all date-format literals must be in the English language. The default date format is American. You can explicitly request another date format by using a format string.
Time literals are in the English language only.

Date literals

A date literal specifies a day, month, and year using any of the following formats, enclosed in single quotation marks ( ' ' ).

This is the syntax for a date literal:

Syntax

{ d ’yyyy-mm-dd’ } | mm-dd-yyyy | mm/dd/yyyy | mm-dd-yy | mm/dd/yy | yyyy-mm-dd | yyyy/mm/dd | dd-mon-yyyy | dd/mon/yyyy | dd-mon-yy | dd/mon/yy

{ d 'yyyy-mm-dd' }

A date literal enclosed in an escape clause is compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase d. End the literal with a close brace )}).

Note: If you use the ODBC escape clause, you must specify the date using the format yyyy-mm-dd.

dd

Specifies the day of the month as a one– or two–digit number in the range 01 to 31.

mm

Specifies the month value as a one– or two–digit number in the range 01 to 12.

mon

Specifies the first three characters of the name of the month in the range 'JAN' to 'DEC'.

yy

Specifies the last two digits of the year.

yyyy

Specifies the year as a four–digit number.

Examples

The following example illustrates how to use the date literal format with an INSERT statement:

INSERT INTO dtest VALUES ( { d '2004-05-07' } )

The INSERT and SELECT statements in the following example show some of the supported formats for date literals:

CREATE TABLE T2 (C1 DATE, C2 TIME); INSERT INTO T2 (C1) VALUES('5/7/56'); INSERT INTO T2 (C1) VALUES('7/MAY/1956'); INSERT INTO T2 (C1) VALUES('1956/05/07'); INSERT INTO T2 (C1) VALUES({d '1956-05-07'}); INSERT INTO T2 (C1) VALUES('29-SEP-1952'); SELECT C1 FROM T2; -------------- c1 1956-05-07 1956-05-07 1956-05-07 1956-05-07 1952-09-29

Time literals

Time literals specify an hour, minute, second, and millisecond, using the following format, enclosed in single quotation marks (' ' ).

This is the syntax for a time literal:

Syntax

{ t 'hh:mi:ss' } | hh:mi:ss[:mls ]

{ t 'hh:mi:ss' }

A time literal enclosed in an escape clause is compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase t. End the literal with a close brace ( } ).

Note: If you use the ODBC escape clause, you must specify the time using the format hh:mi:ss.

hh

Specifies the hour value as a two-digit number in the range 00 to 23.

mi

Specifies the minute value as a two-digit number in the range 00 to 59.

ss

Specifies the seconds value as a two-digit number in the range 00 to 59.

mls

Specifies the milliseconds value as a three-digit number in the range 000 to 999.

Examples

The following example illustrates how to use the time literal format with an INSERT statement:

INSERT INTO ttest VALUES ( { t '23:22:12' } ) ;

The INSERT statements in the following example show some of the formats SQL will and will not accept for time literals:

INSERT INTO T2 (C2) VALUES('3'); error(-20234): Invalid time string INSERT INTO T2 (C2) VALUES('8:30'); error(-20234): Invalid time string INSERT INTO T2 (C2) VALUES('8:30:1'); INSERT INTO T2 (C2) VALUES('8:30:'); error(-20234): Invalid time string INSERT INTO T2 (C2) VALUES('8:30:00'); INSERT INTO T2 (C2) VALUES('8:30:1:1'); INSERT INTO T2 (C2) VALUES({t'8:30:1:1'});

The SELECT statement in the following example illustrates which INSERT statements successfully inserted a row:

SELECT C2 FROM T2;c2 c2 -- 08:30:01 08:30:00 08:30:01 08:30:01

Timestamp literals

Timestamp literals specify a date and a time separated by a space, enclosed in single quotation marks ( ' ' ).

This is the syntax for a timestamp literal:

Syntax

{ ts 'yyyy-mm-dd hh:mi:ss' } | 'date_literal time_literal'

{ ts 'yyyy-mm-dd hh:mi:ss' }

A timestamp literal enclosed in an escape clause is compatible with ODBC. Precede the literal string with an open brace ( { ) and a lowercase ts. End the literal with a close brace ( } ). Note that braces are part of the syntax.

Note: If you use the ODBC escape clause, you must specify the timestamp using the format yyyy-mm-dd hh:mi:ss.

date_literal

A date.

time_literal

A time literal.

Examples

The following example is example illustrates how to INSERT a timestamp literal into a column:

INSERT INTO DTEST VALUES ( { ts '1956-05-07 10:41:37'} ) ;

The following example illustrates a timestamp literal with the ODBC escape clause:

SELECT * FROM DTEST WHERE C1 = {ts '1985-08-10 05:41:37'} ;